﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Runtime.InteropServices;
using Access = Microsoft.Office.Interop.Access;

namespace Macl
{
    [ComVisible(true)]
    [Guid("AB04D7CC-24BC-449f-8E46-AF3A445A3A03")]
    [ClassInterface(ClassInterfaceType.None)]
    [ProgId("M3DatabaseUtilities")]
    public class M3Dac : IM3Dac
    {   
        public bool GenerateDACVars(string myTable)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                VBIDE.VBProject VBProj;
                VBIDE.VBComponent VBComp;
                String cs = oAccess.ADOConnectString;
                using (OleDbConnection con = new OleDbConnection(cs))
                {
                    con.Open();
                    Console.WriteLine("Made the connection to the database");
                    String cmd = String.Format("SELECT * FROM {0}", myTable);
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter { 
                        SelectCommand = new OleDbCommand(cmd, con) })
                    {
                        using (DataSet ds = new DataSet())
                        {
                            adapter.Fill(ds, "Employee");
                            DataTable item = ds.Tables[0];


                            VBProj = oAccess.VBE.VBProjects.Item(1);
                            VBComp = VBProj.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_ClassModule);
                            //VBComp.Name = myTable;                            
                            foreach (DataColumn col in item.Columns)
                                VBComp.CodeModule.AddFromString("Private m" + col.ColumnName +
                                    Environment.NewLine + "'''''''''''''''''''''''" +
                                    Environment.NewLine + "'" + col.ColumnName + " Property" +
                                    Environment.NewLine + "'''''''''''''''''''''''" +
                                    Environment.NewLine + "Public Property Get " + 
                                    col.ColumnName + " As " + col.DataType.Name + 
                                    Environment.NewLine + "   " + col.ColumnName + " = " +
                                    "m" + col.ColumnName + Environment.NewLine + "End Property" + 
                                    Environment.NewLine + "Public Property Let " + col.ColumnName + 
                                    " (Value As " + col.DataType.Name + ")" + Environment.NewLine +
                                    "   " + "m" + col.ColumnName + " = Value" + Environment.NewLine + "End Property");
                        }
                    }
                    con.Close();
                    return true;
                }
            }
            catch (Exception)
            {
                throw; 
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);   
            }
        }        
    }
}
